package com.springapi.dao;

import com.springapi.entity.Maillist;
import com.springapi.entity.Users;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.domain.Pageable;

import java.util.Date;
import java.util.List;

/**
 * 快递列表(Maillist)表数据库访问层
 *
 * @author makejava
 * @since 2025-01-13 20:37:14
 */
public interface MaillistDao {


    /**
     * 通过appid和shopid以及shelfnumber（货架号）查询目前最新取件码
     */
    @Select("SELECT   COALESCE(MAX(pkcode), 0)+1 AS pkcode from maillist where appid=#{appid}  and shopid=#{shopid}  and  shelfnumber=#{shelfnumber}  ")
    Integer getnewpkcode(@Param("appid") int appid, @Param("shopid") int shopid , @Param("shelfnumber") String shelfnumber );


    /**
     * 通过appid和shopid查询店铺全部包裹
     */
    @Select("SELECT  count(id) as id from maillist where appid=#{appid}  and shopid=#{shopid}  and isdelete='否' ")
    Integer findbyin(@Param("appid") int appid, @Param("shopid") int shopid );


    /**
     * 通过appid和shopid查询店铺未出库包裹
     */
    @Select("SELECT  count(id) as id from maillist where appid=#{appid}  and shopid=#{shopid} and isout='否' and isdelete='否' ")
    Integer findbynoout(@Param("appid") int appid, @Param("shopid") int shopid );


    /**
     * 通过appid和shopid查询店铺指定时间段入库包裹数量
     */
    @Select("SELECT  count(id) as id from maillist where appid=#{appid}  and shopid=#{shopid} and intime >=#{intime1}  and intime < #{intime2} ")
    Integer findbyintime(@Param("appid") int appid, @Param("shopid") int shopid, @Param("intime1") Date intime1, @Param("intime2") Date intime2 );


    /**
     * 通过appid和shopid查询店铺指定时间段出库包裹数量
     */
    @Select("SELECT  count(id) as id from maillist where appid=#{appid}  and shopid=#{shopid} and outtime >=#{outtime1}  and outtime < #{outtime2} ")
    Integer findbyouttime(@Param("appid") int appid, @Param("shopid") int shopid, @Param("outtime1") Date outtime1, @Param("outtime2") Date outtime2 );


    /**
     * 通过appid和shopid查询店铺超过入库时间多少天没有出库的异常件数量
     */
    @Select("SELECT  count(id) as id from maillist where appid=#{appid}  and shopid=#{shopid} and DATEDIFF(CURDATE(), intime) > #{days} and isout='否'  ")
    Integer findbyabnormal(@Param("appid") int appid, @Param("shopid") int shopid,@Param("days") Integer days );

    /**
     * 通过appid和shopid查询店铺超过入库时间多少天没有出库的异常件
     */
    @Select("SELECT  * from maillist where appid=#{appid}  and shopid=#{shopid} and DATEDIFF(CURDATE(), intime) > #{days} and isout='否'  ")
    List<Maillist> findlistbyabnormal(@Param("appid") int appid, @Param("shopid") int shopid,@Param("days") Integer days );


    /**
     * 通过appid和shopid查询店铺发送短信失败的包裹
     */
    @Select("SELECT  * from maillist where appid=#{appid}  and shopid=#{shopid} and issendmsg !='是' and isout='否'  order by intime desc ")
    List<Maillist> findlistbysendmsg(@Param("appid") int appid, @Param("shopid") int shopid );



    /**
     * 通过appid和shopid分页查询数据,可传id，shelfnumber，name，mobile
     */
    @Select("SELECT * FROM maillist WHERE appid=#{appid} " +
            "AND shopid=#{shopid} " +
            "AND isdelete='否' " +
            "AND (isout=#{isout} OR #{isout} IS NULL OR #{isout} = '') " +
            "AND (waybill LIKE CONCAT('%', #{waybill}, '%') OR #{waybill} IS NULL OR #{waybill} = '') " +
            "AND (pkcodestr LIKE CONCAT('%', #{pkcodestr}, '%') OR #{pkcodestr} IS NULL OR #{pkcodestr} = '') " +
            "AND (name LIKE CONCAT('%', #{name}, '%') OR #{name} IS NULL OR #{name} = '') " +
            "AND (mobile LIKE CONCAT('%', #{mobile}, '%') OR #{mobile} IS NULL OR #{mobile} = '')")
    List<Maillist> myfindAll(@Param("appid") int appid,
                             @Param("shopid") int shopid,
                             @Param("isout") String isout,
                             @Param("waybill") String waybill,
                             @Param("pkcodestr") String pkcodestr,
                             @Param("name") String name,
                             @Param("mobile") String mobile);

    //根据运单号查询包裹信息
    @Select("select   *  from  maillist WHERE  isdelete='否' and  appid=#{appid} AND shopid=#{shopid} and waybill=#{waybill} ")
    List<Maillist> findbywaybill(@Param("appid") int appid, @Param("shopid") int shopid,@Param("waybill") String waybill);

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    Maillist queryById(Integer id);

    /**
     * 查询指定行数据
     *
     * @param maillist 查询条件
     * @param pageable         分页对象
     * @return 对象列表
     */
    List<Maillist> queryAllByLimit(Maillist maillist, @Param("pageable") Pageable pageable);

    /**
     * 统计总行数
     *
     * @param maillist 查询条件
     * @return 总行数
     */
    long count(Maillist maillist);

    /**
     * 新增数据
     *
     * @param maillist 实例对象
     * @return 影响行数
     */
    int insert(Maillist maillist);

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<Maillist> 实例对象列表
     * @return 影响行数
     */
    int insertBatch(@Param("entities") List<Maillist> entities);

    /**
     * 批量新增或按主键更新数据（MyBatis原生foreach方法）
     *
     * @param entities List<Maillist> 实例对象列表
     * @return 影响行数
     * @throws org.springframework.jdbc.BadSqlGrammarException 入参是空List的时候会抛SQL语句错误的异常，请自行校验入参
     */
    int insertOrUpdateBatch(@Param("entities") List<Maillist> entities);

    /**
     * 修改数据
     *
     * @param maillist 实例对象
     * @return 影响行数
     */
    int update(Maillist maillist);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Integer id);

}

